{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pydea\n",
    "import matplotlib as plt\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 根据论文《林业上市公司融资效率的DEA分析》\n",
    "\n",
    "### 输入数据读取：总资产（X1）、负债总额（X2）、所有者权益（X3）、营业总成本（X4）、筹资活动的现金流入量（X5）\n",
    "\n",
    "### 输出数据读取：营业收入（Y1） 和净利润（Y2）\n",
    "\n",
    "### 模型选择：BBC模型"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 输入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "stockNum = \"600519\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "报表日期        偿还债务支付的现金\n",
       "20210331     22000000\n",
       "20201231            0\n",
       "20200930            0\n",
       "20200630            0\n",
       "              ...    \n",
       "20001231     55200000\n",
       "20000630     47792000\n",
       "19991231    108190000\n",
       "19981231     20000000\n",
       "19700101            0\n",
       "Name: 32, Length: 83, dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#读取csv文件\n",
    "\n",
    "data = pd.read_csv(\"./Assets_and_liabilities/\" + stockNum + \".csv\", encoding=\"utf-8\")\n",
    "# 资产总计\n",
    "total_assets = data.iloc[46, ]\n",
    "# 负债合计\n",
    "total_liabilities = data.iloc[80, :]\n",
    "# 所有者权益\n",
    "owners_equity = data.iloc[81, :]\n",
    "# 营业总成本\n",
    "data_total_operating_costs = pd.read_csv(\"./Total_operating_costs/000596-profit.csv\", encoding=\"utf-8\")\n",
    "total_operating_costs = data_total_operating_costs.iloc[3, :]\n",
    "# 筹资活动的现金流入量\n",
    "data_Funds_inflow_for_financing_activities = pd.read_csv(\"./Funds_inflow_for_financing_activities/000596-flow.csv\", encoding=\"utf-8\")\n",
    "funds_inflow_for_financing_activities = data_Funds_inflow_for_financing_activities.iloc[32, :]\n",
    "funds_inflow_for_financing_activities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 输出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "报表日期              五、净利润\n",
       "20210331    839225582.2\n",
       "20201231     1847888183\n",
       "20200930     1532565307\n",
       "20200630     1006441527\n",
       "               ...     \n",
       "19960630      133653539\n",
       "19951231      147048988\n",
       "19941231      112961204\n",
       "19931231      107833958\n",
       "19700101              0\n",
       "Name: 21, Length: 94, dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 营业收入\n",
    "operating_income = data_total_operating_costs.iloc[2,:]\n",
    "# 净利润\n",
    "profit = data_total_operating_costs.iloc[21,:]\n",
    "profit"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 输入输出数据分别合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                    46            80   81            3           32\n",
      "20210331  2.152180e+11  3.272842e+10  0.0  3.031459e+09  22000000.0\n",
      "20201231  2.133960e+11  4.567513e+10  0.0  7.878037e+09         0.0\n",
      "20200930  1.845790e+11  3.040754e+10  0.0  6.048516e+09         0.0\n",
      "20200630  1.721980e+11  2.967999e+10  0.0  4.162706e+09         0.0\n",
      "20200331  1.816250e+11  2.588991e+10  0.0  2.443222e+09         0.0\n",
      "...                ...           ...  ...           ...         ...\n",
      "19961231  0.000000e+00  0.000000e+00  0.0  2.933809e+08         0.0\n",
      "19960630  0.000000e+00  0.000000e+00  0.0  0.000000e+00         0.0\n",
      "19951231  0.000000e+00  0.000000e+00  0.0  5.565298e+08         0.0\n",
      "19941231  0.000000e+00  0.000000e+00  0.0  4.125243e+08         0.0\n",
      "19931231  0.000000e+00  0.000000e+00  0.0  4.121197e+08         0.0\n",
      "\n",
      "[93 rows x 5 columns]\n",
      "                    2             21\n",
      "20210331  4.130016e+09  8.392256e+08\n",
      "20201231  1.029206e+10  1.847888e+09\n",
      "20200930  8.069421e+09  1.532565e+09\n",
      "20200630  5.519621e+09  1.006442e+09\n",
      "20200331  3.281369e+09  6.309315e+08\n",
      "...                ...           ...\n",
      "19960630  6.950829e+08  1.336535e+08\n",
      "19951231  7.664678e+08  1.470490e+08\n",
      "19941231  5.776666e+08  1.129612e+08\n",
      "19931231  5.721403e+08  1.078340e+08\n",
      "19700101  0.000000e+00  0.000000e+00\n",
      "\n",
      "[93 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# 输入数据合并\n",
    "inputData = pd.concat([total_assets, total_liabilities, owners_equity, total_operating_costs, funds_inflow_for_financing_activities], axis=1, sort=False)\n",
    "# inputData.rename(columns={'46':'资产总计','80':'负债合计', \"81\":'所有者权益', '3':'营业总成本', '32':'偿还债务支付的现金'}) \n",
    "inputData = inputData.iloc[1:, :].astype(\"float64\")\n",
    "inputData = inputData.fillna(value=0)\n",
    "print(inputData)\n",
    "\n",
    "outputData = pd.concat([operating_income, profit], axis=1, sort=False)\n",
    "# outputData = outputData.rename(columns={'2':'营业收入','21':'净利润'})\n",
    "outputData = outputData.iloc[1:, :].astype(\"float64\")\n",
    "outputData = outputData.fillna(value=0)\n",
    "print(outputData)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 执行DEA计算，计算综合效率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#执行计算\n",
    "uni_prob_CRS = pydea.DEAProblem(inputData, outputData, returns='CRS')\n",
    "myresults_CRS = uni_prob_CRS.solve()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 执行DEA计算，计算纯技术效率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#执行计算\n",
    "uni_prob_VRS = pydea.DEAProblem(inputData, outputData, returns='VRS')\n",
    "myresults_VRS = uni_prob_VRS.solve()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>综合效率</th>\n",
       "      <th>CRS_Status</th>\n",
       "      <th>纯技术效率</th>\n",
       "      <th>VRS_Status</th>\n",
       "      <th>规模效率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20210331</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>9.062926e-01</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20201231</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20200930</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>9.974306e-01</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20200630</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>9.855959e-01</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20200331</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19961231</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>8.124086e-09</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19960630</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Infeasible</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>Infeasible</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19951231</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>3.311397e-09</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19941231</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>3.013876e-09</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19931231</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>1.000000e-12</td>\n",
       "      <td>Optimal</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>93 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          综合效率  CRS_Status         纯技术效率  VRS_Status  规模效率\n",
       "20210331   0.0     Optimal  9.062926e-01     Optimal   0.0\n",
       "20201231   0.0     Optimal  1.000000e+00     Optimal   0.0\n",
       "20200930   0.0     Optimal  9.974306e-01     Optimal   0.0\n",
       "20200630   0.0     Optimal  9.855959e-01     Optimal   0.0\n",
       "20200331   0.0     Optimal  1.000000e+00     Optimal   0.0\n",
       "...        ...         ...           ...         ...   ...\n",
       "19961231   0.0     Optimal  8.124086e-09     Optimal   0.0\n",
       "19960630   0.0  Infeasible  0.000000e+00  Infeasible   0.0\n",
       "19951231   0.0     Optimal  3.311397e-09     Optimal   0.0\n",
       "19941231   0.0     Optimal  3.013876e-09     Optimal   0.0\n",
       "19931231   0.0     Optimal  1.000000e-12     Optimal   0.0\n",
       "\n",
       "[93 rows x 5 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# myresults_CRS['综合效率'] = myresults_CRS['Efficiency']\n",
    "# myresults_VRS['纯技术效率'] = myresults_VRS['Efficiency']\n",
    "myresults = pd.DataFrame()\n",
    "myresults['综合效率'] = myresults_CRS['Efficiency']\n",
    "myresults['CRS_Status'] = myresults_CRS['Status']\n",
    "myresults['纯技术效率'] = myresults_VRS['Efficiency']\n",
    "myresults['VRS_Status'] = myresults_VRS['Status']\n",
    "myresults['规模效率'] = (myresults_CRS['Efficiency'] / myresults_VRS['Efficiency']).fillna(0)\n",
    "myresults\n",
    "# myresults['规模效率']\n",
    "# myresults"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 输出数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# resultData = pd.concat([myresults['Efficiency'], myresults['Status'], myresults['Weights']], axis=1)\n",
    "# resultData.to_csv(\"CRS_Result.csv\", encoding=\"utf-8\")\n",
    "# resultData = pd.concat([myresults_CRS['综合效率'], myresults_CRS['CRS_Status'], myresults_VRS['纯技术效率'], myresults_VRS['VRS_Status'], myresults['规模效率']], axis=1)\n",
    "# resultData = resultData.columns(['综合效率', 'CRS_Status', '纯技术效率', 'VRS_Status', '规模效率'])\n",
    "# resultData_new = pd.DataFrame(resultData)\n",
    "# resultData\n",
    "# resultData.to_csv(\"CRS_Result\" + stockNum + \".csv\", encoding=\"utf-8\")\n",
    "myresults.to_csv(\"DeaResultFrom\" + stockNum + \".csv\", encoding=\"utf_8_sig\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
